package com.peter.db;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

public class Compareable {
public static final String url = "jdbc:sqlserver://192.168.7.166; DatabaseName=A69992013";

public static final String name = "com.microsoft.sqlserver.jdbc.SQLServerDriver";

public static final String user = "sa";

public static final String password = "hnhtxx";

public static PreparedStatement pst = null;

public static final String SQLForGetTables = "select name from sys.tables";

public static String errTable;

public static String db1 = "A69992013";

public static String db2 = "A69992014";

public static String IP1 = "192.168.7.164";

public static String IP2 = "127.0.0.1:1434";

public static int tableAccount = 0;

public static int columnAccount = 0;

public static String tableMessage1 ;

public static String columnMessage ;

// 此类的目的是比较两个数据库表的字段

@SuppressWarnings("rawtypes")

public static void main(String[] args) {
// 先是获得数据库连接

Connection con1 = getDbCon(IP1, db1, "sa", "sa123456");

Connection con2 = getDbCon(IP2, db2, "sa", "sa123456");

// 再查询数据库表信息

List list = getInfos(con1, SQLForGetTables);

List list2 = getInfos(con2, SQLForGetTables);

System.out.println("将"+IP1+"的数据库"+db1+"与"+IP2+"数据库"+db2+"进行比较");

tableMessage1="数据库"+db2+"缺少的表为： ";

String res = checkTables(list, list2, con1, con2);

System.out.println(tableMessage1);

System.out.println("-------------------------比较完成");

System.out.println();

System.out.println("将"+IP2+"的数据库"+db2+"与"+IP1+"与数据库"+db1+"进行比较");

tableMessage1="数据库"+db1+"缺少的表为：";

res = checkTables(list2, list, con2, con1);

System.out.println(tableMessage1);

System.out.println("-------------------------比较完成");

System.out.println("这两个数据库中 有"+tableAccount+"个表不相同");

System.out.println("这两个数据库中 有"+columnAccount+"个字段不相同");

}

// 比较两个LIST中的数据是否一致

@SuppressWarnings("rawtypes")

private static String checkTables(List list, List list2, Connection con1,

Connection con2) {
// 先将数据库1的数据与2比较

int table = 0;

int colum = 0;

for (Object object : list) {
table = 0;

for (Object object2 : list2) {
if (object.equals(object2)) {
colum = checkDetail(object, object2, con1, con2);

table=1;

continue;

}

}

if(table==0){
errTable = getName(object);

tableAccount++;

tableMessage1 = tableMessage1 +getName(object)+"; ";

table = 1;

continue;

}

}

if (table == 0 && colum == 0) {
return "两个数据库中表和字段一致";

}

if (table == 0 && colum == 1) {
return "两个数据库中表一致 字段不一致";

}

if (table == 1 && colum == 0) {
return "两个数据库中表不一致 字段一致";

} else

return "两个数据库中表不一致 字段也不一致";

}

// 比较两个表字段是否一致

@SuppressWarnings("rawtypes")

private static int checkDetail(Object object, Object object2,

Connection con1, Connection con2) {
// 获得表的名字

String table = getName(object);

errTable = table;

String sql = "Select Name FROM SysColumns Where id=Object_Id('" + table

+ "')";

// 查询出字段名称

List list = getInfos(con1, sql);

List list2 = getInfos(con2, sql);

return checkColunm(list, list2);

}

// 检查表中的字段是否一致

@SuppressWarnings("rawtypes")

private static int checkColunm(List list, List list2) {
int res = 0;

for (Object object : list) {
int result = 0;

for (Object object2 : list2) {
// 如果这个字段在全部数据都匹配不到 则证明这个字段不匹配

if (object.equals(object2)) {
result = 1;

break;

}

}

if (result == 0) {
columnAccount++;

System.out.println("请注意 !表" + errTable + "中的" + getName(object)

+ "字段在后者数据库中没有");

res = 1;

}

}

return res;

}

// 查询数据库里面的表信息

@SuppressWarnings({ "rawtypes", "unchecked" })

private static List getInfos(Connection con1, String sql) {
Statement stmt;

List list = new ArrayList();

try {
stmt = con1.createStatement();

ResultSet rs = stmt.executeQuery(sql);

ResultSetMetaData md = rs.getMetaData();

int columnCount = md.getColumnCount();

while (rs.next()) {
Map map = new HashMap();

for (int i = 1; i <= columnCount; i++) {
map.put(md.getColumnName(i), rs.getObject(i));

}

list.add(map);

}

} catch (SQLException e) {
e.printStackTrace();

}

return list;

}

/**

* 这是获得连接的方法 传入几个参数就获得连接信息

*

* @param ip

* @param dbName

* @param user

* @param pwd

* @return

*/

private static Connection getDbCon(String ip, String dbName, String user,

String pwd) {
Connection con = null;

try {
Class.forName(name);

String url = "jdbc:sqlserver://" + ip + "; DatabaseName=" + dbName;

con = DriverManager.getConnection(url, user, pwd);

} catch (ClassNotFoundException e) {
e.printStackTrace();

} catch (SQLException e) {
e.printStackTrace();

}

return con;

}

// 根据OBJECT获得name的值

private static String getName(Object object) {
String name = object.toString().substring(

object.toString().indexOf("=") + 1,

object.toString().indexOf("}"));

return name;

}

}